package com.etc.dao.impl;

import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.etc.dao.AdminDao;
import com.etc.entity.Admin;
import com.etc.entity.Business;
import com.etc.entity.Customer;
import com.etc.entity.Games;
import com.etc.entity.GamesType;
import com.etc.util.DBUtil;
import com.sun.org.apache.regexp.internal.recompile;

public class AdminDaoImpl implements AdminDao {

	@Override
	public Admin getAdmin(String mobile, String password) {
		Admin admin = null;
		ResultSet rs =  DBUtil.query("select admin_id,nickname,mobile,password from admin where mobile = ? and password = ?", mobile,password);
		try {
			while (rs.next()) {
				Integer adminId = rs.getInt("admin_id");
				String nickname = rs.getString("nickname");
				String mobile1 = rs.getString("mobile");
				String password1 = rs.getString("password");
				
				
				admin = new Admin();
				admin.setAdminId(adminId);
				admin.setNickname(nickname);
				admin.setMobile(mobile1);
				admin.setPassword(password1);
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		System.out.println(admin==null);
		return admin;
	}

	@Override
	public List<Customer> getPageCustomerByCondition(String nickNameKeyWord,int pageNum,int pageSize) {
		// TODO Auto-generated method stub
		List<Customer> list = new ArrayList<Customer>();
		StringBuffer sql = new StringBuffer();
		sql.append(" select")
		.append(" customer_id,")
		.append(" nickname,")
		.append(" mobile,")
		.append(" password,")
		.append(" status,")
		.append(" image")
		.append(" from customer");
		
		if(1>0) {
			sql.append(" where 1 = 1");
		}
		if(nickNameKeyWord != null && !"".equals(nickNameKeyWord)) {
			sql.append(" and nickname like'"+ nickNameKeyWord + "'");
		}
		
		sql.append(" limit " +(pageNum-1)*pageSize + "," + pageSize);
		ResultSet rs = DBUtil.query(sql.toString());
		try {
			while(rs.next()) {
				Integer customerId = rs.getInt("customer_id");
				String nickname = rs.getString("nickname");
				String mobile1 = rs.getString("mobile");
				String password1 = rs.getString("password");
				Integer status = rs.getInt("status");
				String image = rs.getString("image");
				
				Customer customer = new Customer();
				customer.setCustomerId(customerId);
				customer.setNickname(nickname);
				customer.setMobile(mobile1);
				customer.setPassword(password1);
				customer.setStatus(status);
				customer.setImage(image);
				
				list.add(customer);
				
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public int countCustomerByCondition(String nickNameKeyWord) {
		// TODO Auto-generated method stub
		int total = 0;
		StringBuffer sql = new StringBuffer();
		sql.append("select")
		.append(" count(*)")
		.append(" from customer");
		
		if(1>0) {
			sql.append(" where 1 = 1");
		}
		if(nickNameKeyWord != null && !"".equals(nickNameKeyWord)) {
			sql.append(" and nickname like'"+ nickNameKeyWord + "'");
		}
		
		ResultSet rs = DBUtil.query(sql.toString());
		try {
			while(rs.next()) {
				 total = rs.getInt(1); 
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return total;
	}

	@Override
	public int deleteCustomer(int CustomerId) {
		// TODO Auto-generated method stub
		return DBUtil.update("delete from customer where customer_id = ?", CustomerId);
	}

	@Override
	public int addCustomer(Customer customer) {
		// TODO Auto-generated method stub
		return DBUtil.update("insert into customer(nickname,mobile,password,status,image) values(?,?,?,?,?)",
				customer.getNickname(),
				customer.getMobile(),
				customer.getPassword(),
				customer.getStatus(),
				customer.getImage());
	}

	@Override
	public int updateById(Customer customer) {
		// TODO Auto-generated method stub
		return DBUtil.update("update customer set nickname = ?,mobile = ?,password = ?,status = ?,image = ? where customer_id=?",
				customer.getNickname(),
				customer.getMobile(),
				customer.getPassword(),
				customer.getStatus(),
				customer.getImage(),
				customer.getCustomerId());
	}

	@Override
	public Customer findById(int customerId) {
		// TODO Auto-generated method stub
		StringBuffer sql = new StringBuffer();
		Customer customer = null;
		sql.append(" select")
		.append(" customer_id,")
		.append(" nickname,")
		.append(" mobile,")
		.append(" password,")
		.append(" status,")
		.append(" image")
		.append(" from customer")
		.append(" where customer_id = ?");
		
		ResultSet rs = DBUtil.query(sql.toString(),customerId);
		try {
			while(rs.next()) {
				Integer customerId1 = rs.getInt("customer_id");
				String nickname = rs.getString("nickname");
				String mobile = rs.getString("mobile");
				String password = rs.getString("password");
				Integer status = rs.getInt("status");
				String image = rs.getString("image");
				
				customer = new Customer();
				customer.setCustomerId(customerId1);
				customer.setNickname(nickname);
				customer.setMobile(mobile);
				customer.setPassword(password);
				customer.setStatus(status);
				customer.setImage(image);

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return customer;
	
	}

	@Override
	public List<Business> getPageBusinessByCondition(String nickNameKeyWord, int pageNum, int pageSize) {
		// TODO Auto-generated method stub
		List<Business> list = new ArrayList<Business>();
		StringBuffer sql = new StringBuffer();
		sql.append(" select")
		.append(" business_id,")
		.append(" nickname,")
		.append(" mobile,")
		.append(" password,")
		.append(" status")
		.append(" from business");
		
		if(1>0) {
			sql.append(" where 1 = 1");
		}
		if(nickNameKeyWord != null && !"".equals(nickNameKeyWord)) {
			sql.append(" and nickname like'"+ nickNameKeyWord + "'");
		}
		
		sql.append(" limit " +(pageNum-1)*pageSize + "," + pageSize);
		ResultSet rs = DBUtil.query(sql.toString());
		try {
			while(rs.next()) {
				Integer businessId = rs.getInt("business_id");
				String nickname = rs.getString("nickname");
				String mobile1 = rs.getString("mobile");
				String password1 = rs.getString("password");
				Integer status = rs.getInt("status");
				
				Business business = new Business();
				business.setBusinessId(businessId);
				business.setNickname(nickname);
				business.setMobile(mobile1);
				business.setPassword(password1);
				business.setStatus(status);

				
				list.add(business);
				
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public int countBusinessByCondition(String nickNameKeyWord) {
		// TODO Auto-generated method stub
		int total = 0;
		StringBuffer sql = new StringBuffer();
		sql.append("select")
		.append(" count(*)")
		.append(" from business");
		
		if(1>0) {
			sql.append(" where 1 = 1");
		}
		if(nickNameKeyWord != null && !"".equals(nickNameKeyWord)) {
			sql.append(" and nickname like'"+ nickNameKeyWord + "'");
		}
		
		ResultSet rs = DBUtil.query(sql.toString());
		try {
			while(rs.next()) {
				 total = rs.getInt(1); 
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return total;
	}

	@Override
	public int deleteBusiness(int businessId) {
		// TODO Auto-generated method stub
		return DBUtil.update("delete from business where business_id = ?", businessId);
	}

	@Override
	public int addBusiness(Business business) {
		// TODO Auto-generated method stub
		return DBUtil.update("insert into business(nickname,mobile,password,status) values(?,?,?,?)",
				business.getNickname(),
				business.getMobile(),
				business.getPassword(),
				business.getStatus());
	
	}

	@Override
	public int updateBusinessById(Business business) {
		// TODO Auto-generated method stub
		return DBUtil.update("update business set nickname = ?,mobile = ?,password = ?,status = ? where business=?",
				business.getNickname(),
				business.getMobile(),
				business.getPassword(),
				business.getStatus(),
				business.getBusinessId());
	}

	@Override
	public Business findBusinessById(int businessId) {
		// TODO Auto-generated method stub
		StringBuffer sql = new StringBuffer();
		Business business = null;
		sql.append(" select")
		.append(" business_id,")
		.append(" nickname,")
		.append(" mobile,")
		.append(" password,")
		.append(" status")
		.append(" from business")
		.append(" where business_id = ?");
		
		ResultSet rs = DBUtil.query(sql.toString(),businessId);
		try {
			while(rs.next()) {
				Integer businessId1 = rs.getInt("business_id");
				String nickname = rs.getString("nickname");
				String mobile1 = rs.getString("mobile");
				String password1 = rs.getString("password");
				Integer status = rs.getInt("status");
				
				business = new Business();
				business.setBusinessId(businessId1);
				business.setNickname(nickname);
				business.setMobile(mobile1);
				business.setPassword(password1);
				business.setStatus(status);

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return business;
	
	}

	@Override
	public int updateBusinessStatus(int businessId, int status) {
		// TODO Auto-generated method stub
		StringBuffer sql = new StringBuffer();
		sql.append(" update")
		.append(" business")
		.append(" set")
		.append(" status = ?")
		.append(" where business_id = ?");
		
		return DBUtil.update(sql.toString(),status,businessId);

	}

	@Override
	public List<Games> getPageGamesListByCondition(String nickNameKeyWord, int pageNum, int pageSize) {
		// TODO Auto-generated method stub
		List<Games> list = new ArrayList<Games>();
		StringBuffer sql = new StringBuffer();
		sql.append(" select")
		.append(" a.games_id,")
		.append(" a.games_name,")
		.append(" a.price,")
		.append(" a.type_id,")
		.append(" a.status,")
		.append(" a.image,")
		.append(" a.content,")
		.append(" a.business_id,")
		.append(" b.type_name,")
		.append(" c.nickname,")
		.append(" c.mobile,")
		.append(" c.status")
		.append(" from games a")
		.append(" inner join games_type b")
		.append(" on a.type_id = b.type_id")
		.append(" inner join business c")
		.append(" on a.business_id = c.business_id");
		
		if(1>0) {
			sql.append(" where 1 = 1");
		}
		if(nickNameKeyWord != null && !"".equals(nickNameKeyWord)) {
			sql.append(" and a.games_name like'"+ nickNameKeyWord + "'");
		}
		
		sql.append(" limit " +(pageNum-1)*pageSize + "," + pageSize);
		ResultSet rs = DBUtil.query(sql.toString());
		try {
			while(rs.next()) {
				Integer gamesId = rs.getInt("games_id");
				String gamesName = rs.getString("games_name");
				BigDecimal price = rs.getBigDecimal("price");
				Integer typeId = rs.getInt("type_id");
				Integer gamesStatus = rs.getInt(5);
				String image = rs.getString("image");
				String content = rs.getString("content");
				Integer businessId = rs.getInt("business_id");
				String typeName = rs.getString("type_name");
				String nickname = rs.getString("nickname");
				String mobile = rs.getString("mobile");
				Integer businessStatus = rs.getInt(12);
				
				Games games = new Games();
				games.setGamesId(gamesId);
				games.setGamesName(gamesName);
				games.setPrice(price);
				games.setTypeId(typeId);
				games.setStatus(gamesStatus);
				games.setImage(image);
				games.setContent(content);
				games.setBusinessId(businessId);
				Business business = new Business();
				business.setNickname(nickname);
				business.setMobile(mobile);
				business.setStatus(businessStatus);
				GamesType gamesType = new GamesType();
				gamesType.setTypeName(typeName);
				games.setBusiness(business);
				games.setGamesType(gamesType);
				
				
				list.add(games);
				
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
		
	}

	@Override
	public int updateGameStatus(int gamesId, int status) {
		// TODO Auto-generated method stub
		StringBuffer sql = new StringBuffer();
		sql.append(" update")
		.append(" games")
		.append(" set")
		.append(" status = ?")
		.append(" where games_id = ?");
		
		return DBUtil.update(sql.toString(),status,gamesId);
	}

	@Override
	public int countGamesByCondition(String nickNameKeyWord) {
		// TODO Auto-generated method stub
		int total = 0;
		StringBuffer sql = new StringBuffer();
		sql.append("select")
		.append(" count(*)")
		.append(" from games");
		
		if(1>0) {
			sql.append(" where 1 = 1");
		}
		if(nickNameKeyWord != null && !"".equals(nickNameKeyWord)) {
			sql.append(" and games_id like'"+ nickNameKeyWord + "'");
		}
		
		ResultSet rs = DBUtil.query(sql.toString());
		try {
			while(rs.next()) {
				 total = rs.getInt(1); 
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return total;
	}

	@Override
	public List<Customer> getCustomersList() {
		// TODO Auto-generated method stub
		// TODO Auto-generated method stub
				StringBuffer sql = new StringBuffer();
				Customer customer = null;
				List<Customer> list = new ArrayList<Customer>();
				sql.append(" select")
				.append(" customer_id,")
				.append(" nickname,")
				.append(" mobile,")
				.append(" password,")
				.append(" status,")
				.append(" image")
				.append(" from customer");
				
				ResultSet rs = DBUtil.query(sql.toString());
				try {
					while(rs.next()) {
						Integer customerId = rs.getInt("customer_id");
						String nickname = rs.getString("nickname");
						String mobile = rs.getString("mobile");
						String password = rs.getString("password");
						Integer status = rs.getInt("status");
						String image = rs.getString("image");
						
						customer = new Customer();
						customer.setCustomerId(customerId);
						customer.setNickname(nickname);
						customer.setMobile(mobile);
						customer.setPassword(password);
						customer.setStatus(status);
						customer.setImage(image);
						list.add(customer);

					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				return list;
			
	}

	@Override
	public List<Business> getBusinesssList() {
		// TODO Auto-generated method stub
		StringBuffer sql = new StringBuffer();
		Business business = null;
		List<Business> list = new ArrayList<Business>();
		sql.append(" select")
		.append(" business_id,")
		.append(" nickname,")
		.append(" mobile,")
		.append(" password,")
		.append(" status")
		.append(" from business");
		
		ResultSet rs = DBUtil.query(sql.toString());
		try {
			while(rs.next()) {
				Integer businessId = rs.getInt("business_id");
				String nickname = rs.getString("nickname");
				String mobile = rs.getString("mobile");
				String password = rs.getString("password");
				Integer status = rs.getInt("status");
				
				business = new Business();
				business.setBusinessId(businessId);
				business.setNickname(nickname);
				business.setMobile(mobile);
				business.setPassword(password);
				business.setStatus(status);
				
				list.add(business);

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	
	}

}
